package jee.boot.module.iexp.excel.template;

import jee.boot.common.utils.StringUtils;
import jee.boot.common.utils.excel.annotation.ExcelField;
import jee.boot.entity.task.IExportField;
import jee.boot.entity.task.IExportModel;
import jee.boot.entity.file.Attachment;
import jee.boot.module.iexp.excel.excel.ExcelImport;
import net.sf.jxls.transformer.XLSTransformer;
import net.sf.jxls.util.Util;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

public class DemoTemplate {
    public static void main1(String[] args) {
        ByteArrayOutputStream bos = null;
        try {

            Workbook workBook1 = transformXLS("C:\\data\\template_1.xlsx");
            Workbook workBook2 = transformXLS("C:\\data\\template_2.xlsx");
            Workbook workBookReport = new XSSFWorkbook();
            copySheet(workBook1.getSheet("Sheet1"), workBookReport.createSheet("from_template1"));
            copySheet(workBook2.getSheet("Sheet1"), workBookReport.createSheet("from_template2"));
            bos = new ByteArrayOutputStream();
            workBook1.write(bos);
            byte[] b = bos.toByteArray();
            FileOutputStream os = new FileOutputStream("C:\\data\\report.xlsx");
            os.write(b);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != bos) {
                    bos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static Workbook transformXLS(String template) throws Exception {

        InputStream is = new FileInputStream(template);
        Map<String, Object> data = new HashMap<>();
        initData(data);
        // 关联模板
        XLSTransformer transformer = new XLSTransformer();

        Workbook workBook = transformer.transformXLS(is, data);


        return workBook;
    }

    public static void main2(String[] args) {
        // 根据用户的权限,假设需要导出两种共计6个sheet
        List<String> sheetNames = new ArrayList<String>() {{
            add("group_a");
            add("group_b");
            add("group_c");
            add("count_a");
            add("count_b");
            add("count_c");
            add("test_c");
        }};
        Map<String, Map<String, Object>> param = new HashMap<>();
        // 获取模板信息,模板中共计包含 group,count,test三种模板sheet
        String template = "C:\\data\\template_abc.xlsx";

        //模板输出文件
        String report = "C:\\data\\report.xlsx";
        ByteArrayOutputStream bos = null;
        for (String sheetName : sheetNames) {
            param.put(sheetName, createParam(sheetName));
        }
        Workbook workbook = templateWorkBook(template);
        XLSTransformer transformer = new XLSTransformer();

        int numberOfSheets = workbook.getNumberOfSheets();
        for (String sheetName : sheetNames) {
            int sheetIndex = workbook.getSheetIndex(StringUtils.substringBeforeLast(sheetName, "_"));
            Sheet newSheet = workbook.cloneSheet(sheetIndex);
            workbook.setSheetName(numberOfSheets, sheetName);
            for (int i = newSheet.getFirstRowNum(), c = newSheet.getLastRowNum(); i <= c; i++) {
                org.apache.poi.ss.usermodel.Row srcRow = newSheet.getRow(i);
                if (srcRow != null) {
                    if (srcRow.getFirstCellNum() >= 0 && srcRow.getLastCellNum() >= 0) {
                        for (int j = srcRow.getFirstCellNum(), c1 = srcRow.getLastCellNum(); j <= c1; j++) {
                            org.apache.poi.ss.usermodel.Cell oldCell = srcRow.getCell(j);
                            if (oldCell != null) {
                                if (oldCell.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) {
                                    String oldValue = oldCell.getRichStringCellValue().getString();
                                    String newValue = replaceExpressions(oldValue, "demoData", sheetName);
                                    oldCell.setCellValue(workbook.getCreationHelper().createRichTextString(newValue));
                                }
                            }
                        }
                    }
                }
            }
            numberOfSheets++;
        }
        workbook.removeSheetAt(0);
        workbook.removeSheetAt(0);
        workbook.removeSheetAt(0);

        transformer.transformWorkbook(workbook, param);

        for (int k = 0, w = workbook.getNumberOfSheets(); k < w; k++) {
            Sheet newSheet = workbook.getSheetAt(k);
            for (int i = newSheet.getFirstRowNum(), c = newSheet.getLastRowNum(); i <= c; i++) {
                org.apache.poi.ss.usermodel.Row srcRow = newSheet.getRow(i);
                if (srcRow != null) {
                    if (srcRow.getFirstCellNum() >= 0 && srcRow.getLastCellNum() >= 0) {
                        for (int j = srcRow.getFirstCellNum(), c1 = srcRow.getLastCellNum(); j <= c1; j++) {
                            org.apache.poi.ss.usermodel.Cell oldCell = srcRow.getCell(j);
                            if (oldCell != null) {
                                if (oldCell.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING) {
                                    String oldValue = oldCell.getRichStringCellValue().getString();
                                    if (StringUtils.startsWith(oldValue, "=")) {
                                        oldCell.setCellFormula(StringUtils.removeStart(oldValue, "="));
                                    }
//                                    String newValue = replaceExpressions(oldValue, "demoData", sheetName);
//                                    oldCell.setCellValue(workbook.getCreationHelper().createRichTextString(newValue));
                                }
                            }
                        }
                    }
                }
            }
        }

        try {
            bos = new ByteArrayOutputStream();

            workbook.write(bos);
            byte[] b = bos.toByteArray();
            FileOutputStream os = new FileOutputStream(report);
            os.write(b);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != bos) {
                    bos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static Map<String, Object> createParam(String sheetName) {
        Map<String, Object> map = new HashMap<>();
        map.put("name", "name:" + sheetName);
        map.put("count", 100);
        map.put("gongshi", "=INDIRECT(\"'count_a'!\"&\"A2\")+INDIRECT(\"'count_b'!\"&\"A2\")+INDIRECT(\"'count_c'!\"&\"A2\")");
        return map;
    }

    /**
     * 读取模板,转换成Workbook(带脚本,未解析)
     *
     * @param template
     * @return
     * @throws FileNotFoundException
     */
    public static Workbook templateWorkBook(String template) {
        Workbook xssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(template);
            xssfWorkbook = WorkbookFactory.create(is);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return xssfWorkbook;
    }


    public static void initData(Map<String, Object> map) {
        map.put("gongshi", "=INDIRECT(\"'group1'!\"&\"G2\")");
        map.put("group", "group1");
        map.put("depts", new ArrayList<Map<String, Object>>() {{
            add(new HashMap<String, Object>() {
                {
                    put("name", "研发部");
                    put("emp", new ArrayList<Map<String, Object>>() {{
                        add(new HashMap<String, Object>() {{
                            put("name", "志远");
                            put("code", "001");
                            put("amount", 100);
                        }});
                        add(new HashMap<String, Object>() {{
                            put("name", "李枭");
                            put("code", "002");
                            put("amount", 200);
                        }});
                        add(new HashMap<String, Object>() {{
                            put("name", "王哥");
                            put("code", "003");
                            put("amount", 300);
                        }});
                    }});
                }
            });
            add(new HashMap<String, Object>() {
                {
                    put("name", "财务部");
                    put("emp", new ArrayList<Map<String, Object>>() {{
                        add(new HashMap<String, Object>() {{
                            put("name", "杀姐姐");
                            put("code", "004");
                            put("amount", 400);
                        }});
                        add(new HashMap<String, Object>() {{
                            put("name", "爽");
                            put("code", "005");
                            put("amount", 500);
                        }});
                        add(new HashMap<String, Object>() {{
                            put("name", "萌总");
                            put("code", "006");
                            put("amount", 600);
                        }});
                    }});
                }
            });
        }});
    }


    /**
     * 复制sheet页
     *
     * @param srcSheet
     * @param destSheet
     */
    public static void copySheet(Sheet srcSheet, Sheet destSheet) throws Exception {
        Util.copySheets(destSheet, srcSheet);
        Util.copyPageSetup(destSheet, srcSheet);
        Util.copyPrintSetup(destSheet, srcSheet);
        Util.copyConditionalFormatting(destSheet, srcSheet);
//        ByteArrayOutputStream out = new ByteArrayOutputStream();
//        ((XSSFSheet)srcSheet).write (out);
//        ((XSSFSheet)destSheet).read(new ByteArrayInputStream(out.toByteArray()));
    }

    public static String replaceExpressions(String originalExpression,
                                            String expressionToReplace, String expressionReplacement) {
        /* assuming that:
           - originalExpression may contain expressions of the form ${expr}
           - expressionToReplace is an identifier (alphanumeric + underscore)
           - expressionReplacement is an identifier too
           - only root value may be replaced
        */
        return originalExpression.replaceAll(
                "(\\$\\{)" + expressionToReplace + "((\\b.*)?\\})",
                "$1" + expressionReplacement + "$2");
    }

    public static void main44(String[] args) {
        try {
            long a=System.currentTimeMillis();
            List<FooDemo> list = new ExcelImport(new File("C:\\data\\1812非物料.xml"), 2, "Sheet1").getDataList(FooDemo.class);
            long b=System.currentTimeMillis();
            System.out.println("用时:"+(b-a)+"ms");
            System.out.println(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    class FooDemo {
        @ExcelField(title = "", sort = 1)
        private String a;
        @ExcelField(title = "", sort = 1)
        private String b;
        @ExcelField(title = "", sort = 1)
        private String c;
        @ExcelField(title = "", sort = 1)
        private String d;
        @ExcelField(title = "", sort = 2)
        private String e;
        @ExcelField(title = "", sort = 3)
        private String f;
        @ExcelField(title = "", sort = 4)
        private String g;
        @ExcelField(title = "", sort = 5)
        private String h;
        @ExcelField(title = "", sort = 6)
        private String i;
        @ExcelField(title = "", sort = 7)
        private String j;
        @ExcelField(title = "", sort = 8)
        private String k;
        @ExcelField(title = "", sort = 9)
        private String l;
        @ExcelField(title = "", sort = 10)

        public String getA() {
            return a;
        }

        public void setA(String a) {
            this.a = a;
        }

        public String getB() {
            return b;
        }

        public void setB(String b) {
            this.b = b;
        }

        public String getC() {
            return c;
        }

        public void setC(String c) {
            this.c = c;
        }

        public String getD() {
            return d;
        }

        public void setD(String d) {
            this.d = d;
        }

        public String getE() {
            return e;
        }

        public void setE(String e) {
            this.e = e;
        }

        public String getF() {
            return f;
        }

        public void setF(String f) {
            this.f = f;
        }

        public String getG() {
            return g;
        }

        public void setG(String g) {
            this.g = g;
        }

        public String getH() {
            return h;
        }

        public void setH(String h) {
            this.h = h;
        }

        public String getI() {
            return i;
        }

        public void setI(String i) {
            this.i = i;
        }

        public String getJ() {
            return j;
        }

        public void setJ(String j) {
            this.j = j;
        }

        public String getK() {
            return k;
        }

        public void setK(String k) {
            this.k = k;
        }

        public String getL() {
            return l;
        }

        public void setL(String l) {
            this.l = l;
        }
    }

    public static void main5(String[] args) {
        Map<String, Object> queryData=new HashMap<>();
        queryData.put("time",new Date());
        queryData.put("a",1);
        queryData.put("format","yyyy-MM-dd HH:mm:ss");
        Map<String, Object> data=new HashMap<>();
        data.put("list",new ArrayList<Map<String,Object>>(){{
            add(new HashMap<String,Object>(){{
                put("time",new Date());
            }});
        }});
        IExportModel model=new IExportModel() {
            @Override
            public String getTitle() {
                return null;
            }

            @Override
            public List<? extends IExportField> getExportFieldList() {
                return new ArrayList<IExportField>(){{
                    add(new IExportField() {
                        @Override
                        public String getTitle() {
                            return "日期";
                        }

                        @Override
                        public String getFieldName() {
                            return "time";
                        }

                        @Override
                        public Class<?> getFieldType() {
                            return Date.class;
                        }

                        @Override
                        public String getDictType() {
                            return null;
                        }

                        @Override
                        public String getDictField() {
                            return null;
                        }

                        @Override
                        public String getFormat() {
                            return "yyyy-MM-dd HH:mm:ss";
                        }
                    });
                }};
            }
        };
        try {

            new ExportTemplateExcelExecutor()
//                    .initByModelAndData(model,data)
                    .initByTemplatePathAndData("C:\\data\\datetime.xlsx",queryData)
                    .buildExcel()
                    .writeFile("C:\\data\\datetime_data.xlsx").dispose();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        File file=new File("C:\\data\\Expense_2002_Estimation Template_120宏.xlsm");
        try {
            ExcelImport ei=new ExcelImport(file);
            List<Attachment> dataList = ei.getDataList(Attachment.class);
            System.out.println(dataList);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
    }
}
